package com.example.jegotrip.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.example.jegotrip.entity.NoteInfo;
import com.example.jegotrip.entity.UserInfo;
import com.example.jegotrip.utils.DateUtil;

import java.util.ArrayList;
import java.util.List;

public class NoteDBHelper extends SQLiteOpenHelper {
    // 数据库名称
    private static final String DATABASE_NAME = "note.db";
    // 数据库版本
    private static final int DATABASE_VERSION = 1;
    private static NoteDBHelper noteDBHelper;

    public NoteDBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, @Nullable int version) {
        super(context, name, factory, version);
    }

    public static synchronized NoteDBHelper getInstance(Context context){
        if (noteDBHelper == null) {
            noteDBHelper = new NoteDBHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        return noteDBHelper;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 创建users表的SQL语句
        String CREATE_TABLE = "CREATE TABLE IF NOT EXISTS notes (note_id INTEGER PRIMARY KEY autoincrement default 10," +
                "username TEXT," + //用户名
                "note_title TEXT," + //标题
                "note_content TEXT," + //内容
                "note_create_time TEXT," + //时间
                "note_is_top INTEGER)"; //是否置顶
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 升级数据库时执行的SQL语句
        db.execSQL("DROP TABLE IF EXISTS notes");
        onCreate(db);
    }

    /**
     * 新增/创建备忘录
     */
    public int createNote(String username,
                          String note_title,
                          String note_content){

        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("note_title", note_title);
        values.put("username", username);
        values.put("note_content", note_content);
        values.put("note_create_time", DateUtil.getCurrentFormattedTime("yyyy-MM-dd"));
        values.put("note_is_top", 0);
        String nullColumnHash = "values(null, ?, ?, ?, ?, ?)";
        int insert = (int)db.insert("notes", nullColumnHash, values);
        db.close();
        return insert;
    }

    public List<NoteInfo> searchNotes(String content){
        SQLiteDatabase db = getWritableDatabase();
        List<NoteInfo> noteInfos = new ArrayList<>();
        String sql = "select note_id,username,note_title,note_content,note_create_time," +
                "note_is_top from notes where note_content like?";
        String[] selectedArgs =  new String[]{"%" + content + "%"};
        Cursor cursor = db.rawQuery(sql, selectedArgs);
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int note_id = cursor.getInt(cursor.getColumnIndex("note_id"));
            @SuppressLint("Range") String userName = cursor.getString(cursor.getColumnIndex("username"));
            @SuppressLint("Range") String note_title = cursor.getString(cursor.getColumnIndex("note_title"));
            @SuppressLint("Range") String note_content = cursor.getString(cursor.getColumnIndex("note_content"));
            @SuppressLint("Range") String note_create_time = cursor.getString(cursor.getColumnIndex("note_create_time"));
            @SuppressLint("Range") int note_is_top = cursor.getInt(cursor.getColumnIndex("note_is_top"));
            NoteInfo noteInfo=new NoteInfo(note_id,userName, note_title, note_content, note_create_time, note_is_top);
            noteInfos.add(noteInfo);
        }
        cursor.close();
        db.close();
        return noteInfos;
    }

    public List<NoteInfo> queryNotes(String username){
        SQLiteDatabase db = getWritableDatabase();
        List<NoteInfo> noteInfos = new ArrayList<>();
        String sql = "select note_id,username,note_title,note_content,note_create_time," +
                "note_is_top from notes where username=?";
        String[] selectedArgs = {username};
        Cursor cursor = db.rawQuery(sql, selectedArgs);
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int note_id = cursor.getInt(cursor.getColumnIndex("note_id"));
            @SuppressLint("Range") String userName = cursor.getString(cursor.getColumnIndex("username"));
            @SuppressLint("Range") String note_title = cursor.getString(cursor.getColumnIndex("note_title"));
            @SuppressLint("Range") String note_content = cursor.getString(cursor.getColumnIndex("note_content"));
            @SuppressLint("Range") String note_create_time = cursor.getString(cursor.getColumnIndex("note_create_time"));
            @SuppressLint("Range") int note_is_top = cursor.getInt(cursor.getColumnIndex("note_is_top"));
            NoteInfo noteInfo=new NoteInfo(note_id,userName, note_title, note_content, note_create_time, note_is_top);
            noteInfos.add(noteInfo);
        }
        cursor.close();
        db.close();
        return noteInfos;
    }

    /**
     * 编辑记事本
     * @param title
     * @param content
     * @return
     */
    public int editNote(int note_id, String title,String content){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("note_title", title);
        values.put("note_content", content);
        int update = db.update("notes", values, "note_id=?", new String[]{note_id + ""});
        db.close();
        return update;
    }

    /**
     * 置顶记事本
     * @param noteInfo
     * @param note_is_top 是否置顶标识
     * @return
     */
    public int isTopNote(NoteInfo noteInfo){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("note_is_top", noteInfo.getNote_is_top() == 1 ?0 : 1);
        int update = db.update("notes", values, "note_id=?", new String[]{noteInfo.getNote_id() + ""});
        db.close();
        return update;
    }

    /**
     * 删除
     */
    public int deleteNote(int note_id){
        SQLiteDatabase db = getWritableDatabase();
        int del = (int)db.delete("notes", "note_id=?", new String[]{note_id + ""});
        db.close();
        return del;
    }

    /**
     * 修改密码
     */
    public int updatePassword(String username, String password){
        SQLiteDatabase db = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("password", password);
        int update = db.update("users", values, "username=?", new String[]{username});
        db.close();
        return update;
    }
}
